from app import db
from datetime import datetime, timedelta
from sqlalchemy import func

class Leaderboard(db.Model):
    """排行榜模型"""
    __tablename__ = 'leaderboards'
    
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False, comment='用户ID')
    period_type = db.Column(db.Enum('daily', 'weekly', 'monthly', 'all_time'), nullable=False, comment='统计周期')
    period_date = db.Column(db.Date, nullable=False, comment='统计日期')
    total_score = db.Column(db.Integer, default=0, comment='总得分')
    exam_count = db.Column(db.Integer, default=0, comment='考试次数')
    pass_count = db.Column(db.Integer, default=0, comment='通过次数')
    avg_score = db.Column(db.Numeric(5, 2), default=0, comment='平均分')
    rank_position = db.Column(db.Integer, default=0, comment='排名位置')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    def __repr__(self):
        return f'<Leaderboard {self.user_id}-{self.period_type}-{self.period_date}>'
    
    def to_dict(self):
        """转换为字典"""
        return {
            'id': self.id,
            'user_id': self.user_id,
            'period_type': self.period_type,
            'period_date': self.period_date.isoformat() if self.period_date else None,
            'total_score': self.total_score,
            'exam_count': self.exam_count,
            'pass_count': self.pass_count,
            'avg_score': float(self.avg_score) if self.avg_score else 0,
            'rank_position': self.rank_position,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }
    
    @classmethod
    def update_user_stats(cls, user_id, period_type='all_time'):
        """更新用户统计数据"""
        from app.models import ExamRecord
        
        # 计算统计日期
        today = datetime.now().date()
        if period_type == 'daily':
            period_date = today
        elif period_type == 'weekly':
            # 获取本周开始日期
            period_date = today - timedelta(days=today.weekday())
        elif period_type == 'monthly':
            # 获取本月开始日期
            period_date = today.replace(day=1)
        else:  # all_time
            period_date = today
        
        # 查询考试记录
        query = ExamRecord.query.filter_by(user_id=user_id)
        
        if period_type == 'daily':
            query = query.filter(func.date(ExamRecord.created_at) == period_date)
        elif period_type == 'weekly':
            week_start = period_date
            week_end = week_start + timedelta(days=6)
            query = query.filter(
                func.date(ExamRecord.created_at) >= week_start,
                func.date(ExamRecord.created_at) <= week_end
            )
        elif period_type == 'monthly':
            month_start = period_date
            if month_start.month == 12:
                month_end = month_start.replace(year=month_start.year + 1, month=1) - timedelta(days=1)
            else:
                month_end = month_start.replace(month=month_start.month + 1) - timedelta(days=1)
            query = query.filter(
                func.date(ExamRecord.created_at) >= month_start,
                func.date(ExamRecord.created_at) <= month_end
            )
        
        records = query.all()
        
        # 计算统计数据
        total_score = sum(record.score for record in records)
        exam_count = len(records)
        pass_count = sum(1 for record in records if record.is_passed)
        avg_score = total_score / exam_count if exam_count > 0 else 0
        
        # 更新或创建排行榜记录
        leaderboard = cls.query.filter_by(
            user_id=user_id,
            period_type=period_type,
            period_date=period_date
        ).first()
        
        if leaderboard:
            leaderboard.total_score = total_score
            leaderboard.exam_count = exam_count
            leaderboard.pass_count = pass_count
            leaderboard.avg_score = avg_score
        else:
            leaderboard = cls(
                user_id=user_id,
                period_type=period_type,
                period_date=period_date,
                total_score=total_score,
                exam_count=exam_count,
                pass_count=pass_count,
                avg_score=avg_score
            )
            db.session.add(leaderboard)
        
        db.session.commit()
        
        # 更新排名
        cls.update_rankings(period_type, period_date)
        
        return leaderboard
    
    @classmethod
    def update_rankings(cls, period_type, period_date):
        """更新排名"""
        # 获取该周期的所有记录，按总分降序排列
        records = cls.query.filter_by(
            period_type=period_type,
            period_date=period_date
        ).order_by(cls.total_score.desc()).all()
        
        # 更新排名
        for i, record in enumerate(records, 1):
            record.rank_position = i
        
        db.session.commit()
    
    @classmethod
    def get_leaderboard(cls, period_type='all_time', page=1, per_page=20):
        """获取排行榜"""
        today = datetime.now().date()
        
        if period_type == 'daily':
            period_date = today
        elif period_type == 'weekly':
            period_date = today - timedelta(days=today.weekday())
        elif period_type == 'monthly':
            period_date = today.replace(day=1)
        else:  # all_time
            period_date = today
        
        query = cls.query.filter_by(
            period_type=period_type,
            period_date=period_date
        ).order_by(cls.rank_position.asc())
        
        # 分页
        pagination = query.paginate(
            page=page,
            per_page=per_page,
            error_out=False
        )
        
        return pagination 